const db = require('../db/database');

class ServerDao {

    constructor(database){
        this.database = database;
    }

    createTable(){
        const sql = `
            create table if not exists server (
                id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
                sys_id INTEGER,
                name TEXT,
                desc TEXT,
                type CHARACTER(1),
                ip TEXT,
                port TEXT,
                username TEXT,
                password TEXT,
                shellpath TEXT,
                cachepath TEXT,
                codepath TEXT,
                grep TEXT
            )
        `;
        return this.database.run(sql);
    }

    insert(params){
        const sql = `
            insert into server(sys_id, name, desc, type, ip, port, username, password, shellpath, cachepath, codepath, grep)
            values(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
        `;
        const data=[params.sysId, params.name, params.desc, params.type, params.ip, params.port, 
            params.username, params.password, params.shellpath, params.cachepath, params.codepath, params.grep]
        return this.database.run(sql, data);
    }

    update(params){
        const sql=`
            update server
                set name=?,
                desc=?,
                type=?,
                ip=?,
                port=?,
                username=?,
                password=?,
                shellpath=?,
                cachepath=?,
                codepath=?,
                grep=?
            where id=?
        `;
        return this.database.run(sql, [params.name, params.desc, params.type, params.ip, params.port, 
            params.username, params.password, params.shellpath, params.cachepath, params.codepath, params.grep, params.id]);
    }

    delete(id){
        const sql=`
            delete from server where id=?
        `;
        return this.database.run(sql, [id]);
    }

    getById(id){
        const sql='select * from server where id=?';
        return this.database.get(sql, [id]);
    }

    all(sysId){
        const sql='select * from server where sys_id=?';
        return this.database.all(sql, [sysId]);
    }
}

const serverDao = new ServerDao(db);

module.exports=serverDao;